package july.android.cakes.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.sql.*;
import java.io.*;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

@Service
@Transactional
public class SPoster {
    @Autowired
    private JdbcTemplate jdbc;
    
    
    //处理上传海报图片
    public void UPPoster(String pic, String team_id) {
        String sql = "insert into lost_message(stu_id,pic) values(?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbc.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[]{"team_id", "pic"});
                ps.setString(1, team_id);
                ps.setString(2, "a");
                return ps;
            }
        }, keyHolder);
        int id = keyHolder.getKey().intValue();
        //System.out.println("id:" + id);
        String filename = id + pic.substring(pic.length() - 4);
        
        july.android.cakes.tools.Rename.renameFile(pic.substring(0, pic.length()-19), pic.substring(pic.length()-19), filename);
        filename = "http:" + File.separator + File.separator + "123.206.198.158:8080" + File.separator + "img" + File.separator + "lostfund" + File.separator + filename;
        //System.out.println(filename);
        this.jdbc.update("update lost_message set pic=? where id=?", filename, id);
    }
    
    //根据传入的社团账号修改社团密码
    public void updatepassw(String id,String passw)throws Exception{
        this.jdbc.update("update teaam_login set passw=? where team_id=?)",passw,id);
    }

    // 根据传入的社团账号以及海报路径插入进校园新闻表，返回校园新闻号
    //根据前面一个方法即时获得的社团账号以及校园新闻号，再调用方法插入到社团与新闻的关联表中
    public void addnews(String id,String pic)throws Exception {
        this.jdbc.update("insert into news(pic) values(?)",pic);
        String str=(String)(this.jdbc.queryForMap("select id from news where pic=?",pic)).get("id");
        this.jdbc.update("insert into team2news(team_id,n_id) values(?,?)",Integer.parseInt(id),Integer.parseInt(str));
    }
    
    //根据传入的社团登录账号id,返回这个账号发布的所有海报id，组织头像，发布海报
    public List<Map<String,Object>>getmynews(String id)throws Exception{
        return this.jdbc.queryForList("select team2news.n_id,team.icon,news.pic from news,team2news,team where news.id=team2news.n_id and team.id=team2news.team_id and team.id=?",id);
    }
    
     //显示全部的校园海报栏，返回头像路径和海报路径
    public List<Map<String,Object>> getnews()throws Exception{
       return this.jdbc.queryForList("select team.icon,news.pic from news,team,team2news where news.id=team2news.n_id and team.id=team2news.team_id");
    }

     //显示校方组织的校园海报栏，返回头像路径和海报路径
    public List<Map<String,Object>> getschoolnews() {
       return this.jdbc.queryForList("select team.icon,news.pic from news,team,team2news where news.id=team2news.n_id and team.id=team2news.team_id and team.ttype='school'");
    }
    
     //显示学生组织的校园海报栏，返回头像路径和海报路径
    public List<Map<String,Object>> getstudentnews(){
        return this.jdbc.queryForList("select team.icon,news.pic from news,team,team2news where news.id=team2news.n_id and team.id=team2news.team_id and team.ttype='student'");
    }

     //删除海报，删除成功后返回字符串，用于后台管理
    public String removenews(String id){    
        System.out.println(id);
         this.jdbc.update("delete from team2news where n_id=?",Integer.parseInt(id));
         this.jdbc.update("delete from news where id=?",Integer.parseInt(id));
         return "true";
    }
    
    //根据传入的社团账号，社团头像路径更新社团的头像
    public void updatedepimage(String id,String icon)throws Exception{
        this.jdbc.update("update team set icon=? where id=?",icon,Integer.parseInt(id));
    }
    

    
    //根据传入的社团账号获取社团的名字 
     public String getdepName(String id)throws Exception{
       return (this.jdbc.queryForMap("select nname from team where id=?",id)).get("nname").toString();
    }

     // 抛出异常
    public void exception() {
        throw new IllegalStateException("产生未知错误");
    }

    public void UPlostfound(String pic, String ttype, String detail, String tel, String stu_id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}
